
# importation des bibliothèques, libairies et modules
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import datetime as dt
import seaborn as sns


import tempfile
from zipfile import ZipFile
import shutil
import os
from fnmatch import fnmatch
import sys
import csv
import glob
import re

import requests
from bs4 import BeautifulSoup
import openpyxl 
from openpyxl import Workbook


# On ne prend que les fichiers excels des classements avant les arrivées
path = r'/Users/andressoto/Downloads/data'
files = glob.glob(path + "/*.xlsx")
files.sort()

df = pd.DataFrame()
#files = glob.glob('/Users/andressoto/Kit-Start-Data/Kit-Start-Data/data/*.xslx')
for i in files :
    excel = pd.read_excel(i, header=0, names = ['Rang', 'Nat./Voile', 'Skipper/Bateau','Heure','Latitude','Longitude','Cap','Vitesse',
                        'VMG','Distance','Cap2','Vitesse2','VMG2','Distance2','Cap3','Vitesse3','VMG3','Distance3',
                        'DTF','DTL'], usecols="B:U", skiprows=4, skipfooter=4, sheet_name="fr", engine="openpyxl")
    excel.insert(5, 'Date', i.split("_")[1] + i.split("_")[2].split('.')[0])
    excel['Date'] = pd.to_datetime(excel['Date'], format='%Y%m%d%H%M%S')
    df = df.append(excel, ignore_index=True)
df.head(50)


# information sur les variables 
df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 23538 entries, 0 to 23537
Data columns (total 21 columns):
 #   Column          Non-Null Count  Dtype         
---  ------          --------------  -----         
 0   Rang            23319 non-null  object        
 1   Nat./Voile      23319 non-null  object        
 2   Skipper/Bateau  23319 non-null  object        
 3   Heure           15426 non-null  object        
 4   Latitude        15426 non-null  object        
 5   Date            23538 non-null  datetime64[ns]
 6   Longitude       15426 non-null  object        
 7   Cap             19576 non-null  object        
 8   Vitesse         19357 non-null  object        
 9   VMG             15426 non-null  object        
 10  Distance        15426 non-null  object        
 11  Cap2            15645 non-null  object        
 12  Vitesse2        15426 non-null  object        
 13  VMG2            19137 non-null  object        
 14  Distance2       15426 non-null  object        
 15  Cap3            19356 non-null  object        
 16  Vitesse3        19357 non-null  object        
 17  VMG3            19357 non-null  object        
 18  Distance3       19357 non-null  object        
 19  DTF             19357 non-null  object        
 20  DTL             19357 non-null  object        
dtypes: datetime64[ns](1), object(20)
memory usage: 3.8+ MB


# Visualisation des valeurs manquantes par variables
VarContexte=df[['Rang', 'Nat./Voile', 'Skipper/Bateau','Heure','Latitude','Longitude','Cap','Vitesse',
                        'VMG','Distance','Cap2','Vitesse2','VMG2','Distance2','Cap3','Vitesse3','VMG3','Distance3',
                        'DTF','DTL']]
info_types = pd.DataFrame(VarContexte.dtypes)

df2 = pd.DataFrame()
var = info_types.index.tolist() #[info_types[0]!="object"]
nb_nan = VarContexte[var].apply(lambda x: x.isnull().sum()).values.tolist()
nb_distincts = VarContexte[var].apply(lambda x: x.nunique()).values.tolist()

df2 ["variables"] = var
df2["Nb value distinct"] = nb_distincts
df2["Nb value missing"] = nb_nan 
df2["%Modalité_missing"] = round((df2["Nb value missing"]/len(df))*100, 2)

print(df2.sort_values(by = '%Modalité_missing', ascending = False))

         variables  Nb value distinct  Nb value missing  %Modalité_missing
3            Heure                 46              8112              34.46
4         Latitude              14852              8112              34.46
5        Longitude              14971              8112              34.46
8              VMG                299              8112              34.46
9         Distance                141              8112              34.46
11        Vitesse2                231              8112              34.46
13       Distance2               1317              8112              34.46
10            Cap2                355              7893              33.53
12            VMG2                304              4401              18.70
14            Cap3                348              4182              17.77
18             DTF              14688              4181              17.76
17       Distance3               3581              4181              17.76
16            VMG3                249              4181              17.76
15        Vitesse3                212              4181              17.76
19             DTL              11656              4181              17.76
7          Vitesse                262              4181              17.76
6              Cap                396              3962              16.83
1       Nat./Voile                 34               219               0.93
2   Skipper/Bateau                 35               219               0.93
0             Rang                 93               219               0.93


# suppression des lignes contenant des NaN
df = df.dropna()


# réinitialisation des index du dataframe
df.reset_index(inplace = True)


# vérification de présence de doublons
df.duplicated().sum()

0


df.shape

(15207, 22)


# fonctions pour retirer le/les derniers éléments d'une variable
def f1(x):
    a = []
    for i in x : 
        i=i[:-1]
        a.append(i)
    return a

def f3(x):
    a = []
    for i in x : 
        i=i[:-3]
        a.append(i)
    return a

def f4(x):
    a = []
    for i in x : 
        i=i[:-4]
        a.append(i)
    return a

def f5(x):
    a = []
    for i in x : 
        i=i[:-5]
        a.append(i)
    return a

# fonction pour retirer le premier élément d'une variable
def f11(x):
    a = []
    for i in x : 
        i=i[1:]
        a.append(i)
    return a

# fonction pour ne garder que les 5 premiers éléments d'une variable
def f55(x):
    a = []
    for i in x : 
        i=i[:5]
        a.append(i)
    return a

# fonction pour ne récupérer que les 3 premiers éléments d'une variable
def f33(x):
    a = []
    for i in x : 
        i=i[:3]
        a.append(i)
    return a

# fonction pour ne garder que les 2 derniers éléments d'une variable
def f2(x):
    a = []
    for i in x : 
        i=i[-2:]
        a.append(i)
    return a

# fonction pour récupérer le deuxième élément après un split
def function(x):
    a=[]
    for i in range(len(x)):
        a.append(df["Nat./Voile"][i].split(" ",1)[1])
    return a

# fonctions pour récupérer les éléments de la variable Nat.Voile
def function2(x):
    a=[]
    for i in range(len(x)):
        a.append(x[i].split("\n",1)[0])
    return a

def function3(x):
    a=[]
    for i in range(len(x)):
        a.append(x[i].split("\n",1)[1])
    return a


# Réécriture des variables 


# création de 2 nouvelles variables à partir de Nat./Voile : Nationalite & Voile
df['Nat./Voile'] = df['Nat./Voile'].astype('str')
df['Nat./Voile']=f11(df['Nat./Voile'])
df['Nat./Voile'] = df['Nat./Voile'].str.replace('R\n', '')

df["Nationalite"] = f33(df["Nat./Voile"])
df["Voile"] = function(df['Nat./Voile'])
df = df.drop('Nat./Voile', 1)

# création de 2 nouvelles variables Skipper/Bateau : Skipper et Bateau
df['Skipper/Bateau'] = df['Skipper/Bateau'].astype('str')
df["Skipper"] = function2(df['Skipper/Bateau'])
df["Bateau"] = function3(df['Skipper/Bateau'])
df = df.drop('Skipper/Bateau', 1)

# Heure
df["Heure"] = f55(df["Heure"])

# Cap
df["Cap"]=f1(df["Cap"])
df["Cap2"]=f1(df["Cap2"])
df["Cap3"]=f1(df["Cap3"])

# Vitesse
df["Vitesse"] = f4(df["Vitesse"])
df["Vitesse2"] = f4(df["Vitesse2"])
df["Vitesse3"] = f4(df["Vitesse3"])

# VMG
df["VMG"] = f4(df["VMG"])
df["VMG2"] = f4(df["VMG2"])
df["VMG3"] = f4(df["VMG3"])

# Distance
df["Distance"] = f3(df["Distance"])
df["Distance2"] = f3(df["Distance2"])
df["Distance3"] = f3(df["Distance3"])

# DTF
df["DTF"] = f3(df["DTF"])

# DTL
df["DTL"] = f3(df["DTL"])

/var/folders/v2/t31mdp6s2wz9yd4rb56ygdt40000gn/T/ipykernel_2747/1040464593.py:11: FutureWarning:

In a future version of pandas all arguments of DataFrame.drop except for the argument 'labels' will be keyword-only

/var/folders/v2/t31mdp6s2wz9yd4rb56ygdt40000gn/T/ipykernel_2747/1040464593.py:17: FutureWarning:

In a future version of pandas all arguments of DataFrame.drop except for the argument 'labels' will be keyword-only


# correction de valeurs 
df["Voile"][df["Voile"] == '10'] = 16
df["Voile"][df["Voile"] == '777'] = 77

/var/folders/v2/t31mdp6s2wz9yd4rb56ygdt40000gn/T/ipykernel_2747/3615876223.py:2: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

/var/folders/v2/t31mdp6s2wz9yd4rb56ygdt40000gn/T/ipykernel_2747/3615876223.py:3: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy


df.head()


# fonction pour la conversion des coordonnées GPS en coordonnées décimales de lattitude et longitude
def conversion(old):
    direction = {'N':1, 'S':-1, 'E': 1, 'W':-1}
    new = str(old).replace('°',' ').replace('.',' ').replace('\'',' ')
    new = new.split()
    new_dir = new.pop()
    new.extend([0,0])
    return (int(new[0])+int(new[1])/60.0+int(new[2])/3600.0) * direction[new_dir]


# on écrase les données par les nouvelles coordonnées calculées 
a=[]
b=[]
for i in range(len(df["Latitude"])):
    i = conversion(df["Latitude"][i])
    a.append(i)

for j in range(len(df["Longitude"])):
    j = conversion(df["Longitude"][j])
    b.append(j)

df["Latitude"] = a 
df["Longitude"] = b


df.head()


# Changement des formats des variables

# format objet to int
df['Rang'] = df['Rang'].astype(int)
df['Cap'] = df['Cap'].astype(int)
df['Cap2'] = df['Cap2'].astype(int)
df['Cap3'] = df['Cap3'].astype(int)
df['Voile'] = df['Voile'].astype(int)

# format objet to float
df['Latitude'] = df['Latitude'].astype(float)
df['Longitude'] = df['Longitude'].astype(float)
df['Vitesse'] = df['Vitesse'].astype(float)
df['Vitesse2'] = df['Vitesse2'].astype(float)
df['Vitesse3'] = df['Vitesse3'].astype(float)
df['VMG'] = df['VMG'].astype(float)
df['VMG2'] = df['VMG2'].astype(float)
df['VMG3'] = df['VMG3'].astype(float)
df['Distance'] = df['Distance'].astype(float)
df['Distance2'] = df['Distance2'].astype(float)
df['Distance3'] = df['Distance3'].astype(float)
df['DTF'] = df['DTF'].astype(float)
df['DTL'] = df['DTL'].astype(float)


df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 15207 entries, 0 to 15206
Data columns (total 24 columns):
 #   Column       Non-Null Count  Dtype         
---  ------       --------------  -----         
 0   index        15207 non-null  int64         
 1   Rang         15207 non-null  int64         
 2   Heure        15207 non-null  object        
 3   Latitude     15207 non-null  float64       
 4   Date         15207 non-null  datetime64[ns]
 5   Longitude    15207 non-null  float64       
 6   Cap          15207 non-null  int64         
 7   Vitesse      15207 non-null  float64       
 8   VMG          15207 non-null  float64       
 9   Distance     15207 non-null  float64       
 10  Cap2         15207 non-null  int64         
 11  Vitesse2     15207 non-null  float64       
 12  VMG2         15207 non-null  float64       
 13  Distance2    15207 non-null  float64       
 14  Cap3         15207 non-null  int64         
 15  Vitesse3     15207 non-null  float64       
 16  VMG3         15207 non-null  float64       
 17  Distance3    15207 non-null  float64       
 18  DTF          15207 non-null  float64       
 19  DTL          15207 non-null  float64       
 20  Nationalite  15207 non-null  object        
 21  Voile        15207 non-null  int64         
 22  Skipper      15207 non-null  object        
 23  Bateau       15207 non-null  object        
dtypes: datetime64[ns](1), float64(13), int64(6), object(4)
memory usage: 2.8+ MB


# conversion de l'ordre de grandeur des variables vitesse (noeuds) en kilomètres par heures (km/h)
df["Vitesse"]  = df["Vitesse"]*1.852
df["Vitesse2"] = df["Vitesse2"]*1.852
df["Vitesse3"] = df["Vitesse3"]*1.852

# conversion de l'ordre de grandeur des variables VMG (noeuds) en kilomètres par heures (km/h)
df["VMG"]  = df["VMG"]*1.852
df["VMG2"]  = df["VMG2"]*1.852
df["VMG3"]  = df["VMG3"]*1.852

# conversion de l'ordre de grandeur des variables distances (nm) en kilomètres (km)
df["Distance"]  = df["Distance"]*1.852
df["Distance2"]  = df["Distance2"]*1.852
df["Distance3"]  = df["Distance3"]*1.852

# conversion de l'ordre de grandeur de la variable DTF (nm) en kilomètres (km)
df["DTF"]  = df["DTF"]*1.852

# conversion de l'ordre de grandeur de la variable DTL (nm) en kilomètres (km)
df["DTL"]  = df["DTL"]*1.852


# suppresion de la variable index
df = df.drop('index', 1)

/var/folders/v2/t31mdp6s2wz9yd4rb56ygdt40000gn/T/ipykernel_2747/1927308674.py:2: FutureWarning:

In a future version of pandas all arguments of DataFrame.drop except for the argument 'labels' will be keyword-only


df.head()


# Web Scarpping
url1 = "https://www.vendeeglobe.org/fr/glossaire"

def caracteristique(url):
    df2 = {}
    page = requests.get(url1)
    soup = BeautifulSoup(page.content, 'html.parser')
    
    class1 = soup.find_all(class_="boats-list__skipper-name") # récupération des noms des skippers
    class2 = soup.find_all(class_="boats-list__popup-specs-list") # récupérations des caractéristiques
    
    Skipper = []
    Num_voile = []
    Anciens_noms_bateau = []
    Architecte = []
    Chantier = []
    Date_lancement = []
    Longueur = []
    Largeur = []
    Tirant_eau = []
    Deplacement_poids = []
    Nb_derives = []
    Hauteur_mat = []
    Voile_quille = []
    Surface_voile_pres = []
    Surface_voile_portant = []
    
    for i in class1:
        Skipper.append(i.text.split(" ")[0] + " " + i.text.split(" ")[1].capitalize())
        
    for j in class2:
        if "Numéro" in j.text: # condition pour contourner les valeurs manquantes 
            Num_voile.append(*re.findall(r'\d+', j.text.split("Numéro de voile : ")[1].split("\n")[0]))
        else: 
            Num_voile.append("NaN")
            
        if "Anciens" in j.text: # condition pour contourner les valeurs manquantes 
            Anciens_noms_bateau.append(j.text.split("Anciens noms du bateau : ")[1].split("\n")[0])
        else : 
            Anciens_noms_bateau.append("NaN")
        Architecte.append(j.text.split("Architecte : ")[1].split("\n")[0])
        Chantier.append(j.text.split("Chantier : ")[1].split("\n")[0])
        Date_lancement.append(j.text.split("Date de lancement : ")[1].split("\n")[0]) # changer le format de la date
        Longueur.append(j.text.split("Longueur : ")[1].split("m")[0].replace(',','.').replace(' ',''))
        Largeur.append(j.text.split("Largeur : ")[1].split("m")[0].replace(',','.').replace(' ',''))
        Tirant_eau.append(j.text.split("Tirant d'eau : ")[1].split("m")[0].replace(',','.').replace(' ',''))
        Deplacement_poids.append(j.text.split("Déplacement (poids) : ")[1].split("\n")[0].replace(',','.').replace('tonnes','').replace('t','').replace(' ',''))
        Nb_derives.append(j.text.split("Nombre de dérives : ")[1].split("\n")[0].replace('asymétriques','').replace(' ',''))
        Hauteur_mat.append(j.text.split("Hauteur mât : ")[1].split("\n")[0].replace(',','.').replace('m','').replace(' ',''))
            
        if "quille" in j.text: # condition pour contourner les valeurs manquantes 
            Voile_quille.append(j.text.split("Voile quille : ")[1].split("\n")[0])
        else : 
            Voile_quille.append("NaN")
            
        Surface_voile_pres.append(j.text.split("Surface de voiles au près : ")[1].split("m")[0].replace(" ",""))
        Surface_voile_portant.append(j.text.split("Surface de voiles au portant : ")[1].split("m")[0].replace(" ",""))
    
    df2 = {'Skipper' : Skipper, 'Num_voile' : Num_voile, 'Anciens_noms_bateau' : Anciens_noms_bateau,
        'Architecte' : Architecte, 'Chantier' : Chantier, 'Date_lancement' : Date_lancement, 
        'Longueur' : Longueur, 'Largeur' : Largeur, 'Tirant_eau' : Tirant_eau, 'Deplacement_poids' : Deplacement_poids,
        'Nb_derives' : Nb_derives, 'Hauteur_mat' : Hauteur_mat, 'Voile_quille' : Voile_quille, 
        'Surface_voile_pres' : Surface_voile_pres, 'Surface_voile_portant' : Surface_voile_portant}
    df2 = pd.DataFrame(df2)
    return df2


# Récupération des données web
df2 = caracteristique(url1)


df2.head()


# Nom_Skipper
df2["Skipper"][df2["Skipper"] == "Stéphane Le"] = "Stéphane Le Diraison"
df2["Skipper"][df2["Skipper"] == "Jean Le"] = "Jean Le Cam"
df2["Skipper"][df2["Skipper"] == "Alan "] = "Alan Roura"
df2["Skipper"][df2["Skipper"] == "Arnaud Boissières"] = "Arnaud Boissieres"
df2["Skipper"][df2["Skipper"] == "Sam Davies"] = "Samantha Davies"


# Architecte
df2["Architecte"][df2["Architecte"] == "Verdier - VPLP"] = "VPLP/Verdier"
df2["Architecte"][df2["Architecte"] == "VPLP - Verdier"] = "VPLP/Verdier"
df2["Architecte"][df2["Architecte"] == "Bruce Farr Yacht Design"] = "Bruce Farr Design"
df2["Architecte"][df2["Architecte"] == "Owen Clarke"] = "Owen Clarke Design"
df2["Architecte"][df2["Architecte"] == "Owen Clarke Design LLP - Clay Oliver"] = "Owen Clarke Design - Clay Oliver"
df2["Architecte"][df2["Architecte"] == "Finot-Conq Design"] = "Groupe Finot-Conq"
df2["Architecte"][df2["Architecte"] == "VPLP - Alex Thomson Racing (led by Pete Hobson)"] = "VPLP - Alex Thomson Racing"


# Correction de noms de chantiers
df2["Chantier"][df2["Chantier"] == "Persico"] = "Persico Marine"
df2["Chantier"][df2["Chantier"] == "Southern Ocean Marine (Nouvelle-Zélande)"] = "Southern Ocean Marine, Nouvelle Zélande"
df2["Chantier"][df2["Chantier"] == "Southern Ocean Marine (Nouvelle Zélande)"] = "Southern Ocean Marine, Nouvelle Zélande"
df2["Chantier"][df2["Chantier"] == "CDK - Mer Agitée"] = "CDK Technologies - Mer Agitée"
df2["Chantier"][df2["Chantier"] == "Hakes Marine - Wellington (Nouvelle-Zélande)"] = "Hakes Marine"
df2["Chantier"][df2["Chantier"] == "CDK Technologies / Assemblage : Team ARKEA PAPREC"] = "CDK Technologies / Team ARKEA PAPREC"


# Correction d'un numéro de voile manquant
df2["Num_voile"][df2["Skipper"] == "Thomas Ruyant"] = 59


# Correction de poids de déplacements manquants
df2["Deplacement_poids"][df2["Skipper"] == "Alan Roura"] = 8
df2["Deplacement_poids"][df2["Skipper"] == "Kevin Escoffier"] = 7.7


# Voile_quille
df2["Voile_quille"][df2["Skipper"] == "Arnaud Boissieres"] = "carbone"
df2["Voile_quille"][df2["Skipper"] == "Manuel Cousin"] = "carbone"
df2["Voile_quille"][df2["Voile_quille"] == "monotype"] = "acier"
df2["Voile_quille"][df2["Voile_quille"] == "NaN"] = "acier"
df2["Voile_quille"][df2["Voile_quille"] == "Acier mécano soudé"] = "acier soudé"
df2["Voile_quille"][df2["Voile_quille"] == "acier mécano soudé"] = "acier soudé"


# Nb_derives 
df2["Nb_derives"][df2["Nb_derives"] == "foiler"] = "foils"
df2["Nb_derives"][df2["Nb_derives"] == "2"] = "no foils"
df2.rename(columns={'Nb_derives': 'Type_derives'}, inplace=True)


# Changement des formats des variables
# format int
df2["Num_voile"] = df2["Num_voile"].astype(int)
df2["Surface_voile_pres"] = df2["Surface_voile_pres"].astype(int)
df2["Surface_voile_portant"] = df2["Surface_voile_portant"].astype(int)

# format float
df2["Longueur"] = df2["Longueur"].astype(float)
df2["Largeur"] = df2["Largeur"].astype(float)
df2["Tirant_eau"] = df2["Tirant_eau"].astype(float)
df2["Deplacement_poids"] = df2["Deplacement_poids"].astype(float)
df2["Hauteur_mat"] = df2["Hauteur_mat"].astype(float)

# format date
for i in range(len(df2["Date_lancement"])):
    df2["Date_lancement"][i] = df2["Date_lancement"][i].replace("Janvier","01").replace("Février","02").replace("Mars", "03").replace("Avril","04").replace("Mai","05").replace("Juin","06").replace("Juillet","07").replace("Août","08").replace("Septembre","09").replace("Octobre","10").replace("Novembre","11").replace("Décembre","12")

for i in range(len(df2["Date_lancement"])): # Convert datetime object to date object.
    df2["Date_lancement"][i] = (dt.datetime.strptime(df2["Date_lancement"][i], "%d %m %Y")).date()
    
#print(d.isoformat())

/var/folders/v2/t31mdp6s2wz9yd4rb56ygdt40000gn/T/ipykernel_2747/3599720165.py:16: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

/var/folders/v2/t31mdp6s2wz9yd4rb56ygdt40000gn/T/ipykernel_2747/3599720165.py:19: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy


df2.head()


print(" Taille df : " + str(df.shape) + "\n",
     "Taille df2 : " + str(df2.shape))

 Taille df : (15207, 23)
 Taille df2 : (33, 15)


# jointure 
df3 = pd.merge(df, df2, how='left', on='Skipper', left_index=False)


df3.columns

Index(['Rang', 'Heure', 'Latitude', 'Date', 'Longitude', 'Cap', 'Vitesse',
       'VMG', 'Distance', 'Cap2', 'Vitesse2', 'VMG2', 'Distance2', 'Cap3',
       'Vitesse3', 'VMG3', 'Distance3', 'DTF', 'DTL', 'Nationalite', 'Voile',
       'Skipper', 'Bateau', 'Num_voile', 'Anciens_noms_bateau', 'Architecte',
       'Chantier', 'Date_lancement', 'Longueur', 'Largeur', 'Tirant_eau',
       'Deplacement_poids', 'Type_derives', 'Hauteur_mat', 'Voile_quille',
       'Surface_voile_pres', 'Surface_voile_portant'],
      dtype='object')


# suppression des 2 variables d'identification Voile et Num_voile car non nécessaire 
columns = ["Voile","Num_voile"]
df3 = df3.drop(columns, axis=1)


df3.head()


df3.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 15207 entries, 0 to 15206
Data columns (total 35 columns):
 #   Column                 Non-Null Count  Dtype         
---  ------                 --------------  -----         
 0   Rang                   15207 non-null  int64         
 1   Heure                  15207 non-null  object        
 2   Latitude               15207 non-null  float64       
 3   Date                   15207 non-null  datetime64[ns]
 4   Longitude              15207 non-null  float64       
 5   Cap                    15207 non-null  int64         
 6   Vitesse                15207 non-null  float64       
 7   VMG                    15207 non-null  float64       
 8   Distance               15207 non-null  float64       
 9   Cap2                   15207 non-null  int64         
 10  Vitesse2               15207 non-null  float64       
 11  VMG2                   15207 non-null  float64       
 12  Distance2              15207 non-null  float64       
 13  Cap3                   15207 non-null  int64         
 14  Vitesse3               15207 non-null  float64       
 15  VMG3                   15207 non-null  float64       
 16  Distance3              15207 non-null  float64       
 17  DTF                    15207 non-null  float64       
 18  DTL                    15207 non-null  float64       
 19  Nationalite            15207 non-null  object        
 20  Skipper                15207 non-null  object        
 21  Bateau                 15207 non-null  object        
 22  Anciens_noms_bateau    15207 non-null  object        
 23  Architecte             15207 non-null  object        
 24  Chantier               15207 non-null  object        
 25  Date_lancement         15207 non-null  object        
 26  Longueur               15207 non-null  float64       
 27  Largeur                15207 non-null  float64       
 28  Tirant_eau             15207 non-null  float64       
 29  Deplacement_poids      15207 non-null  float64       
 30  Type_derives           15207 non-null  object        
 31  Hauteur_mat            15207 non-null  float64       
 32  Voile_quille           15207 non-null  object        
 33  Surface_voile_pres     15207 non-null  int64         
 34  Surface_voile_portant  15207 non-null  int64         
dtypes: datetime64[ns](1), float64(18), int64(6), object(10)
memory usage: 4.2+ MB


# stat descriptives
df3.describe()


# Boxplot des variables quantitatives
plt.figure(figsize=(20, 15))
sns.boxplot(data=df3[['Rang','Latitude','Longitude','Cap','Cap2','Cap3','Vitesse','Vitesse2','Vitesse3','Distance',
                    'Distance3','VMG','VMG2','VMG3','Longueur','Largeur','Tirant_eau',
                      'Deplacement_poids','Hauteur_mat','Surface_voile_pres','Surface_voile_portant']], orient='h')

<AxesSubplot:>


# Boxplot des variables quantitatives
plt.figure(figsize=(20, 15))
sns.boxplot(data=df3[['Distance2','DTF','DTL']], orient='h')

<AxesSubplot:>


# Nombre de nationalités différentes 
df3[["Nationalite","Skipper"]].nunique()

Nationalite     8
Skipper        33
dtype: int64


# Liste des nationalités représentées
df3[["Nationalite"]][63:96].value_counts()

Nationalite
FRA            25
GBR             2
ESP             1
FIN             1
ITA             1
JPN             1
MON             1
SUI             1
dtype: int64


# Diagramme des proportions des types de dérives sur l'ensemble du dataframe
name = ['Foils', 'Classiques']
data = [df3[df3["Type_derives"]=="foils"].count()[0], df3[df3["Type_derives"]=="classiques"].count()[0]]
data2 = [df2[df2["Type_derives"]=="foils"].count()[0], df2[df2["Type_derives"]=="classiques"].count()[0]]

fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(15,5))
fig.suptitle('Horizontally stacked subplots')
explode=(0, 0.15)
ax1.pie(data, explode=explode, labels=name, autopct='%1.1f%%', startangle=90, shadow=True) # plt.pie
ax2.pie(data2, explode=explode, labels=name, autopct='%1.1f%%', startangle=90, shadow=True)
plt.axis('equal')
plt.show()

print("Parmi l'ensemble du dataframe : " + "                     " + "Parmi les 33 skippers : " + "\n" + 
      "Nombre total de foils : " + str(df3[df3["Type_derives"]=="foils"].count()[0]) + "                         " + "Nombre de voiliers avec foils : " +
      str(df2[df2["Type_derives"]=="foils"].count()[0]) +
      "\n" + "Nombre total sans foils : " + str(df3[df3["Type_derives"]=="classiques"].count()[0]) + "                       " +
        "Nombre de voiliers sans foils : " + str(df2[df2["Type_derives"]=="classiques"].count()[0]))

Parmi l'ensemble du dataframe :                      Parmi les 33 skippers : 
Nombre total de foils : 6983                         Nombre de voiliers avec foils : 19
Nombre total sans foils : 6655                       Nombre de voiliers sans foils : 14


# Bar chart des différents voiles de quilles
fig = plt.figure()
ax = fig.add_axes([0,0,1,1])
label = ['acier', 'acier forgé', 'carbone', 'acier soudé', 'inox usiné']
values = df3[["Voile_quille"]][63:96].value_counts()
plot = ax.bar(label,values)
for value in plot:
    height = value.get_height()
    plt.text(value.get_x() + value.get_width()/2.,
             1.002*height,'%d' % int(height), ha='center', va='bottom')
plt.show()


# Architectes
df3[["Architecte"]][63:96].value_counts()

Architecte                      
VPLP/Verdier                        10
Bruce Farr Design                    3
Groupe Finot-Conq                    3
Owen Clarke Design                   3
Bruce Farr design                    2
Juan Kouyoumdjian                    2
VPLP                                 2
Verdier                              2
Lavanos                              1
Marc Lombard                         1
Owen Clarke Design - Clay Oliver     1
Pierre Rolland                       1
Samuel Manuard                       1
VPLP - Alex Thomson Racing           1
dtype: int64


#Visualisation de la corrélation entre les variables 
data = df3[['Rang','Latitude','Longitude','Cap','Cap2','Cap3','Vitesse','Vitesse2','Vitesse3','Distance','Distance2',
                    'Distance3','DTF','DTL','VMG','VMG2','VMG3','Longueur','Largeur','Tirant_eau',
                      'Deplacement_poids','Hauteur_mat','Surface_voile_pres','Surface_voile_portant']]   

corrmat = data.corr()
top_corr_features = corrmat.index
plt.figure(figsize=(25,25))

# Heat map
g=sns.heatmap(data[top_corr_features].corr(),annot=True,cmap="viridis")


# Impact du classement et de la vitesse moyenne des voiliers en fonction de la présence de foils
df4 = df3.groupby(['Skipper','Type_derives']).agg({'Rang' : 'mean','VMG': 'mean'})
df4.reset_index(inplace=True)
fig, axes = plt.subplots(1, 2,figsize=(15, 5))
fig.suptitle("Impact de la présence d'un foil sur le classement et la vitesse moyenne des voiliers")
sns.barplot(ax=axes[0],data=df4,x='Type_derives',y='Rang')
sns.barplot(ax=axes[1],data=df4,x='Type_derives',y='VMG')

<AxesSubplot:xlabel='Type_derives', ylabel='VMG'>


# Visualisation sur l'ensemble de la course
df5 = df3[['Date','Skipper','Vitesse', 'VMG', 'Distance','Type_derives']]
fig, axes = plt.subplots(1, 3,figsize=(15, 5))
fig.suptitle("Impact de la présence d'un foil sur la vitesse, la VMG et la distance depuis le dernier classement")
pd.pivot_table(df5,index='Date',columns='Type_derives',values='Vitesse',aggfunc=np.mean).plot(ax=axes[0],ylabel='Vitesse')
pd.pivot_table(df5,index='Date',columns='Type_derives',values='VMG',aggfunc=np.mean).plot(ax=axes[1],ylabel='VMG')
pd.pivot_table(df5,index='Date',columns='Type_derives',values='Distance',aggfunc=np.mean).plot(ax=axes[2],ylabel='Distance')

<AxesSubplot:xlabel='Date', ylabel='Distance'>


# Histogrammes des caractéristiques techniques 
df6 = df3.groupby(['Skipper','Deplacement_poids', 'Hauteur_mat','Largeur', 'Longueur', 
                            'Surface_voile_portant', 'Surface_voile_pres',
                            "Tirant_eau"]).agg({'Rang' : 'mean','VMG': 'mean'})
df6.reset_index(inplace=True)
fig, axes = plt.subplots(5, 2, figsize=(18, 15))
fig.suptitle("Impact des caractéristiques techniques des voiliers sur le classement et la vitesse moyenne")
l=['Deplacement_poids', 'Hauteur_mat','Largeur', 'Surface_voile_portant', 'Surface_voile_pres']

for i,j in zip(range(5),l):
    sns.barplot(ax=axes[i,0],data=df6, x= j, y='Rang')
for i,j in zip(range(5),l):
    sns.barplot(ax=axes[i,1],data=df6, x= j, y='VMG')


# multiple boxplots
sns.set_theme(style="ticks", palette="pastel")
plt.figure(figsize=(22,8))
sns.boxplot(x="Rang", y="Vitesse",
            hue="Type_derives", palette=["m", "g"],
            data=df3)
sns.despine(offset=10, trim=True)


# moyennes de vitesse par rang et par types de dérives
print(df3.groupby(["Rang", "Type_derives"])["Vitesse"].mean())
print("\n" + "Vitesse moyenne (foils) : " + str(round(df3[df3["Type_derives"] == "foils"]["Vitesse"].mean(),2)) + "\n" +
    "Vitesse moyenne (classique) : " + str(round(df3[df3["Type_derives"] == "classiques"]["Vitesse"].mean(),2)))

Rang  Type_derives
1     classiques      22.924539
      foils           28.067729
2     classiques      24.962314
      foils           27.839196
3     classiques      22.345281
                        ...    
31    classiques      15.931140
      foils           20.934523
32    classiques      17.507573
      foils           21.490982
33    foils            8.124643
Name: Vitesse, Length: 65, dtype: float64

Vitesse moyenne (foils) : 25.72
Vitesse moyenne (classique) : 23.06


# Corrélation entre Rang et VMG
plt.figure(figsize=(8,6))
sns.stripplot (x="Rang",
              y="VMG",
              data=df3)

<AxesSubplot:xlabel='Rang', ylabel='VMG'>


# Visualisation pour les 3 premiers skippers
a = ["Yannick Bestaven", "Charlie Dalin", "Louis Burton"]
fig, axes = plt.subplots(1, 2,figsize=(16, 6))
df.pivot_table(index='Date',columns='Skipper',values='VMG')[a].plot(ax=axes[0],
                                ylabel='VMG',title = 'VMG en fonction de la date')
df.pivot(index='Date',columns='Skipper',values='Rang')[a].plot(ax=axes[1],
                                ylabel='Rang',title = 'Rang en fonction de la date').invert_yaxis()


# VGM et Rang des 3 premiers en fonction de la date
fig, axes = plt.subplots(1, 2,figsize=(16, 6))
df.pivot_table(index='Date',columns='Skipper',values='VMG')[a].resample('W-MON').mean().plot(ax=axes[0],
                                ylabel='VMG',title = 'VMG (des trois premiers arrivés) en fonction de la date ')
df.pivot(index='Date',columns='Skipper',values='Rang')[a].resample('W-MON').mean().plot(ax=axes[1],
                                ylabel='Rang',title = 'Rang (des trois premiers arrivés) en fonction de la date ').invert_yaxis()


# data pour calculer les distances totales par skippers
distance = df3[["Skipper","Distance2"]][31:]
distance


# classement distances totales
distance2 = df3.groupby(['Skipper'])['Distance2'].agg('sum')
distance2.sort_values(ascending=False)

Skipper
Thomas Ruyant           56763.4296
Louis Burton            56413.2164
Boris Herrmann          56023.1852
Yannick Bestaven        56010.0360
Giancarlo Pedote        55491.1056
Benjamin Dutreux        54428.4280
Damien Seguin           54346.0140
Jean Le Cam             54191.3720
Armel Tripon            54150.0724
Maxime Sorel            53116.6564
Jérémie Beyou           53001.2768
Charlie Dalin           51929.5244
Clarisse Cremer         51578.9408
Romain Attanasio        49951.0328
Kojiro Shiraishi        49326.7236
Stéphane Le Diraison    48908.9124
Arnaud Boissieres       48795.9404
Alan Roura              48771.3088
Didac Costa             47926.6116
Pip Hare                47536.9508
Manuel Cousin           47403.9772
Clément Giraud          46180.3608
Miranda Merron          45615.3156
Ari Huusela             43638.1204
Isabelle Joschke        43378.8404
Alexia Barrier          42527.2908
Sébastien Destremau     34256.0736
Sébastien Simon         21089.8352
Samantha Davies         21031.8676
Fabrice Amedeo          20615.9084
Alex Thomson            20425.1524
Kevin Escoffier         19195.9800
Nicolas Troussel         9707.6284
Name: Distance2, dtype: float64


# dataframe des 3 premiers du classement final du Vendée Globe 2020
df9 = df3[df3["Skipper"] == "Yannick Bestaven"]
df10 = df3[df3["Skipper"] == "Charlie Dalin"]
df11 = df3[df3["Skipper"] == "Louis Burton"]


import plotly.graph_objects as go
import pandas as pd

scl = ['rgb(213,62,79)', 'rgb(244,109,67)', 'rgb(253,174,97)', \
    'rgb(254,224,139)', 'rgb(255,255,191)', 'rgb(230,245,152)', \
    'rgb(171,221,164)', 'rgb(102,194,165)', 'rgb(50,136,189)'
]
n_colors = len(scl)

fig = go.Figure()

for i, (lat, lon) in enumerate(zip(df9.columns[2:3], df9.columns[3:4])):
    fig.add_trace(go.Scattergeo(
        lon = df9["Longitude"],
        lat = df9["Latitude"],
        mode = 'lines',
        line = dict(width = 2, color = scl[0]
        ), name = 'Yannick Bestaven (1er)'))

for i, (lat, lon) in enumerate(zip(df10.columns[2:3], df10.columns[3:4])):
    fig.add_trace(go.Scattergeo(
        lon = df10["Longitude"],
        lat = df10["Latitude"],
        mode = 'lines',
        line = dict(width = 2, color = scl[8]
        ), name = 'Charlie Dalin (2ème)'))
    
for i, (lat, lon) in enumerate(zip(df11.columns[2:3], df11.columns[3:4])):
    fig.add_trace(go.Scattergeo(
        lon = df11["Longitude"],
        lat = df11["Latitude"],
        mode = 'lines',
        line = dict(width = 2, color = scl[2]
        ), name = 'Louis Burton (3ème)'))
    
fig.update_layout(
    title_text = 'Contour lines over globe<br>(Click and drag to rotate)',
    showlegend = True,
    geo = dict(
        showland = True,
        showcountries = True,
        showocean = True,
        countrywidth = 0.5,
        landcolor = 'rgb(192, 192, 192)',
        lakecolor = 'rgb(255,255,255)',
        oceancolor = 'rgb(255,255,255)',
        projection = dict(
            type = 'orthographic',
            rotation = dict(
                lon = -100,
                lat = 40,
                roll = 0
            )
        ),
        lonaxis = dict(
            showgrid = True,
            gridcolor = 'rgb(102, 102, 102)',
            gridwidth = 0.5
        ),
        lataxis = dict(
            showgrid = True,
            gridcolor = 'rgb(102, 102, 102)',
            gridwidth = 0.5
        )
    )
)



fig.show()

	Rang	Nat./Voile	Skipper/Bateau	Heure	Latitude	Date	Longitude	Cap	Vitesse	VMG	...	Cap2	Vitesse2	VMG2	Distance2	Cap3	Vitesse3	VMG3	Distance3	DTF	DTL
0	1	\nFRA 18	Louis Burton\nBureau Vallée 2	15:30 FR\n	46°24.46'N	2020-11-08 14:00:00	01°50.48'W	241°	17.7 kts	17.5 kts	...	357°	0.0 kts	0.0 kts	2788.0 nm	201°	0.3 kts	0.3 kts	6.1 nm	24293.9 nm	0.0 nm
1	2	\nMON 10	Boris Herrmann\nSeaexplorer - Yacht Club De Mo...	15:31 FR\n1min	46°24.34'N	2020-11-08 14:00:00	01°49.82'W	241°	11.1 kts	10.9 kts	...	357°	0.0 kts	0.0 kts	2787.9 nm	196°	0.3 kts	0.2 kts	6.0 nm	24294.2 nm	0.4 nm
2	3	\nFRA 8	Jérémie Beyou\nCharal	15:30 FR\n	46°24.91'N	2020-11-08 14:00:00	01°49.99'W	244°	15.5 kts	15.5 kts	...	357°	0.0 kts	0.0 kts	2788.5 nm	199°	0.2 kts	0.2 kts	5.5 nm	24294.3 nm	0.5 nm
3	4	\nFRA 59	Thomas Ruyant\nLinkedOut	15:30 FR\n	46°24.71'N	2020-11-08 14:00:00	01°49.68'W	244°	13.2 kts	13.1 kts	...	357°	0.0 kts	0.0 kts	2788.3 nm	196°	0.2 kts	0.2 kts	5.6 nm	24294.5 nm	0.6 nm
4	5	\nFRA 53	Maxime Sorel\nV And B Mayenne	15:30 FR\n	46°24.59'N	2020-11-08 14:00:00	01°49.56'W	246°	10.9 kts	10.9 kts	...	357°	0.0 kts	0.0 kts	2788.1 nm	195°	0.8 kts	0.7 kts	5.8 nm	24294.5 nm	0.6 nm
5	6	\nFRA 56	Fabrice Amedeo\nNewrest - Art et Fenetres	15:31 FR\n1min	46°25.19'N	2020-11-08 14:00:00	01°49.87'W	238°	16.9 kts	16.9 kts	...	357°	0.0 kts	0.0 kts	2788.7 nm	200°	0.2 kts	0.2 kts	5.3 nm	24294.5 nm	0.7 nm
6	7	\nFRA 01	Jean Le Cam\nYes we Cam !	15:30 FR\n	46°24.90'N	2020-11-08 14:00:00	01°49.49'W	247°	10.7 kts	10.7 kts	...	357°	0.0 kts	0.0 kts	2788.4 nm	196°	0.2 kts	0.2 kts	5.5 nm	24294.7 nm	0.8 nm
7	8	\nGBR 99	Alex Thomson\nHugo Boss	15:30 FR\n	46°25.21'N	2020-11-08 14:00:00	01°49.45'W	238°	13.8 kts	13.7 kts	...	357°	0.0 kts	0.0 kts	2788.7 nm	196°	0.2 kts	0.2 kts	5.2 nm	24294.8 nm	1.0 nm
8	9	FR\nFRA 69	Sébastien Destremau\nMerci	15:31 FR\n1min	46°25.04'N	2020-11-08 14:00:00	01°49.32'W	245°	9.6 kts	9.6 kts	...	357°	0.0 kts	0.0 kts	2788.6 nm	195°	0.2 kts	0.2 kts	5.3 nm	24294.8 nm	1.0 nm
9	10	\nGBR 777	Pip Hare\nMedallia	15:31 FR\n1min	46°25.22'N	2020-11-08 14:00:00	01°49.36'W	245°	12.7 kts	12.7 kts	...	357°	0.0 kts	0.0 kts	2788.8 nm	196°	0.2 kts	0.2 kts	5.1 nm	24294.9 nm	1.0 nm
10	11	\nFRA 85	Kevin Escoffier\nPRB	15:31 FR\n1min	46°25.14'N	2020-11-08 14:00:00	01°49.22'W	235°	13.8 kts	13.6 kts	...	357°	0.0 kts	0.0 kts	2788.7 nm	194°	0.2 kts	0.2 kts	5.1 nm	24294.9 nm	1.1 nm
11	12	\nFRA 92	Stéphane Le Diraison\nTime For Oceans	15:30 FR\n	46°25.05'N	2020-11-08 14:00:00	01°49.15'W	242°	13.1 kts	13.0 kts	...	357°	0.0 kts	0.0 kts	2788.6 nm	194°	0.2 kts	0.2 kts	5.3 nm	24294.9 nm	1.1 nm
12	13	\nFRA 49	Romain Attanasio\nPure - Best Western Hotels a...	15:30 FR\n	46°25.26'N	2020-11-08 14:00:00	01°49.29'W	241°	11.6 kts	11.6 kts	...	357°	0.0 kts	0.0 kts	2788.8 nm	195°	0.2 kts	0.2 kts	5.1 nm	24294.9 nm	1.1 nm
13	14	\nFRA 1000	Damien Seguin\nGroupe APICIL	15:28 FR\n-2min	46°24.84'N	2020-11-08 14:00:00	01°49.02'W	232°	7.5 kts	7.2 kts	...	357°	0.0 kts	0.0 kts	2788.4 nm	192°	0.2 kts	0.2 kts	5.4 nm	24294.9 nm	1.1 nm
14	15	\nFRA 83	Clément Giraud\nCompagnie du lit - Jiliti	15:30 FR\n	46°24.89'N	2020-11-08 14:00:00	01°48.92'W	248°	8.3 kts	8.3 kts	...	357°	0.0 kts	0.0 kts	2788.4 nm	192°	0.2 kts	0.2 kts	5.4 nm	24295.0 nm	1.2 nm
15	16	\nFRA 109	Samantha Davies\nInitiatives - Coeur	15:30 FR\n	46°25.62'N	2020-11-08 14:00:00	01°49.36'W	243°	16.9 kts	16.9 kts	...	357°	0.0 kts	0.0 kts	2789.2 nm	197°	0.1 kts	0.1 kts	4.7 nm	24295.1 nm	1.2 nm
16	17	\nFRA 17	Yannick Bestaven\nMaître Coq IV	15:30 FR\n	46°25.14'N	2020-11-08 14:00:00	01°48.91'W	242°	10.4 kts	10.4 kts	...	357°	0.0 kts	0.0 kts	2788.7 nm	192°	0.2 kts	0.2 kts	5.1 nm	24295.1 nm	1.3 nm
17	18	\nITA 34	Giancarlo Pedote\nPrysmian Group	15:30 FR\n	46°25.53'N	2020-11-08 14:00:00	01°49.09'W	227°	16.7 kts	16.2 kts	...	357°	0.0 kts	0.0 kts	2789.1 nm	195°	0.2 kts	0.2 kts	4.8 nm	24295.2 nm	1.3 nm
18	19	\nJPN 11	Kojiro Shiraishi\nDMG MORI Global One	15:31 FR\n1min	46°25.42'N	2020-11-08 14:00:00	01°49.00'W	241°	12.3 kts	12.3 kts	...	357°	0.0 kts	0.0 kts	2788.9 nm	193°	0.2 kts	0.2 kts	4.9 nm	24295.2 nm	1.3 nm
19	20	\nFRA 6	Nicolas Troussel\nCORUM L'Épargne	15:27 FR\n-3min	46°25.80'N	2020-11-08 14:00:00	01°49.19'W	241°	22.3 kts	22.3 kts	...	357°	0.0 kts	0.0 kts	2789.3 nm	195°	0.2 kts	0.1 kts	4.5 nm	24295.2 nm	1.4 nm
20	21	\nFRA 71	Manuel Cousin\nGroupe Sétin	15:30 FR\n	46°25.16'N	2020-11-08 14:00:00	01°48.60'W	229°	13.4 kts	12.8 kts	...	358°	0.0 kts	0.0 kts	2788.7 nm	190°	0.2 kts	0.2 kts	5.1 nm	24295.3 nm	1.5 nm
21	22	\nFRA 30	Clarisse Cremer\nBanque Populaire X	15:30 FR\n	46°25.78'N	2020-11-08 14:00:00	01°49.03'W	241°	11.8 kts	11.8 kts	...	357°	0.0 kts	0.0 kts	2789.3 nm	195°	0.6 kts	0.4 kts	4.5 nm	24295.3 nm	1.5 nm
22	23	\nSUI 7	Alan Roura\nLa Fabrique	15:30 FR\n	46°25.72'N	2020-11-08 14:00:00	01°48.96'W	239°	12.9 kts	12.9 kts	...	357°	0.0 kts	0.0 kts	2789.2 nm	194°	0.2 kts	0.1 kts	4.5 nm	24295.3 nm	1.5 nm
23	24	\nESP 33	Didac Costa\nOne Planet One Ocean	15:30 FR\n	46°25.59'N	2020-11-08 14:00:00	01°48.85'W	238°	13.4 kts	13.4 kts	...	357°	0.0 kts	0.0 kts	2789.1 nm	193°	0.7 kts	0.6 kts	4.7 nm	24295.4 nm	1.5 nm
24	25	\nFRA 09	Benjamin Dutreux\nOMIA - Water Family	15:29 FR\n-1min	46°25.47'N	2020-11-08 14:00:00	01°48.74'W	247°	11.6 kts	11.6 kts	...	357°	0.0 kts	0.0 kts	2789.0 nm	192°	0.2 kts	0.2 kts	4.8 nm	24295.4 nm	1.5 nm
25	26	FR\nFRA 02	Armel Tripon\nL'Occitane en Provence	15:28 FR\n-2min	46°25.36'N	2020-11-08 14:00:00	01°48.63'W	242°	12.6 kts	12.6 kts	...	358°	0.0 kts	0.0 kts	2788.9 nm	190°	0.2 kts	0.2 kts	4.8 nm	24295.4 nm	1.5 nm
26	27	\nFRA 72	Alexia Barrier\nTSE - 4myplanet	15:30 FR\n	46°25.83'N	2020-11-08 14:00:00	01°48.92'W	236°	10.9 kts	10.8 kts	...	357°	0.0 kts	0.0 kts	2789.4 nm	194°	0.6 kts	0.4 kts	4.5 nm	24295.4 nm	1.6 nm
27	28	\nFRA 27	Isabelle Joschke\nMACSF	15:26 FR\n-4min	46°24.98'N	2020-11-08 14:00:00	01°48.22'W	238°	13.8 kts	13.5 kts	...	358°	0.0 kts	0.0 kts	2788.5 nm	187°	0.2 kts	0.2 kts	5.2 nm	24295.5 nm	1.6 nm
28	29	\nFRA 4	Sébastien Simon\nARKEA PAPREC	15:29 FR\n-1min	46°25.75'N	2020-11-08 14:00:00	01°48.73'W	235°	13.4 kts	13.4 kts	...	357°	0.0 kts	0.0 kts	2789.3 nm	192°	0.2 kts	0.1 kts	4.5 nm	24295.5 nm	1.6 nm
29	30	\nFRA 50	Miranda Merron\nCampagne de France	15:28 FR\n-2min	46°25.39'N	2020-11-08 14:00:00	01°48.34'W	237°	11.4 kts	11.3 kts	...	358°	0.0 kts	0.0 kts	2788.9 nm	188°	0.2 kts	0.2 kts	4.8 nm	24295.6 nm	1.7 nm
30	31	\nFIN 222	Ari Huusela\nStark	15:30 FR\n	46°25.65'N	2020-11-08 14:00:00	01°48.21'W	234°	12.1 kts	12.0 kts	...	358°	0.0 kts	0.0 kts	2789.1 nm	188°	0.2 kts	0.2 kts	4.6 nm	24295.8 nm	1.9 nm
31	NL	\nFRA 79	Charlie Dalin\nAPIVIA	NaN	NaN	2020-11-08 14:00:00	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
32	NL	\nFRA 14	Arnaud Boissieres\nLa Mie Câline - Artisans Ar...	NaN	NaN	2020-11-08 14:00:00	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
33	1	\nFRA 8	Jérémie Beyou\nCharal	18:30 FR\n	46°19.75'N	2020-11-08 17:00:00	03°27.02'W	268°	20.2 kts	16.8 kts	...	268°	23.7 kts	20.6 kts	35.6 nm	261°	3.0 kts	2.8 kts	69.5 nm	24234.9 nm	0.0 nm
34	2	\nFRA 79	Charlie Dalin\nAPIVIA	18:30 FR\n	46°15.92'N	2020-11-08 17:00:00	03°21.99'W	266°	20.1 kts	17.3 kts	...	267°	23.3 kts	20.6 kts	35.0 nm	257°	2.8 kts	2.7 kts	66.8 nm	24235.6 nm	0.6 nm
35	3	\nFRA 59	Thomas Ruyant\nLinkedOut	18:30 FR\n	46°19.06'N	2020-11-08 17:00:00	03°24.12'W	265°	20.9 kts	18.0 kts	...	269°	23.9 kts	20.6 kts	35.8 nm	260°	2.9 kts	2.7 kts	67.6 nm	24236.2 nm	1.2 nm
36	4	\nGBR 99	Alex Thomson\nHugo Boss	18:30 FR\n	46°15.33'N	2020-11-08 17:00:00	03°18.94'W	265°	20.2 kts	17.6 kts	...	264°	21.9 kts	19.9 kts	32.9 nm	256°	2.8 kts	2.7 kts	64.9 nm	24237.0 nm	2.0 nm
37	5	\nMON 10	Boris Herrmann\nSeaexplorer - Yacht Club De Mo...	18:30 FR\n	46°17.04'N	2020-11-08 17:00:00	03°18.71'W	269°	18.8 kts	15.7 kts	...	268°	22.6 kts	19.9 kts	34.0 nm	258°	2.7 kts	2.6 kts	64.3 nm	24238.1 nm	3.1 nm
38	6	\nFRA 6	Nicolas Troussel\nCORUM L'Épargne	18:30 FR\n	46°22.46'N	2020-11-08 17:00:00	03°22.50'W	266°	21.0 kts	17.9 kts	...	271°	22.9 kts	19.4 kts	34.3 nm	263°	2.8 kts	2.6 kts	66.0 nm	24239.0 nm	4.1 nm
39	7	FR\nFRA 02	Armel Tripon\nL'Occitane en Provence	18:30 FR\n	46°21.29'N	2020-11-08 17:00:00	03°19.94'W	270°	21.9 kts	17.8 kts	...	271°	23.6 kts	20.0 kts	35.4 nm	262°	2.7 kts	2.6 kts	64.4 nm	24239.8 nm	4.9 nm
40	8	\nFRA 4	Sébastien Simon\nARKEA PAPREC	18:30 FR\n	46°16.47'N	2020-11-08 17:00:00	03°13.69'W	269°	18.5 kts	15.5 kts	...	267°	20.9 kts	18.5 kts	31.4 nm	257°	2.6 kts	2.5 kts	61.1 nm	24240.6 nm	5.7 nm
41	9	\nFRA 17	Yannick Bestaven\nMaître Coq IV	18:30 FR\n	46°14.56'N	2020-11-08 17:00:00	03°10.61'W	268°	20.2 kts	17.2 kts	...	264°	21.6 kts	19.6 kts	32.4 nm	254°	2.5 kts	2.5 kts	59.5 nm	24241.3 nm	6.4 nm
42	10	\nFRA 109	Samantha Davies\nInitiatives - Coeur	18:30 FR\n	46°20.94'N	2020-11-08 17:00:00	03°16.55'W	271°	19.9 kts	16.1 kts	...	270°	21.6 kts	18.4 kts	32.4 nm	261°	5.9 kts	5.5 kts	62.2 nm	24241.5 nm	6.6 nm
43	11	\nFRA 18	Louis Burton\nBureau Vallée 2	18:30 FR\n	46°21.73'N	2020-11-08 17:00:00	03°17.02'W	271°	20.3 kts	16.4 kts	...	271°	21.5 kts	18.3 kts	32.3 nm	262°	2.7 kts	2.5 kts	62.4 nm	24241.7 nm	6.8 nm
44	12	\nJPN 11	Kojiro Shiraishi\nDMG MORI Global One	18:30 FR\n	46°19.15'N	2020-11-08 17:00:00	03°11.77'W	269°	19.9 kts	16.6 kts	...	267°	22.1 kts	19.5 kts	33.2 nm	259°	2.5 kts	2.4 kts	59.3 nm	24243.2 nm	8.3 nm
45	13	\nITA 34	Giancarlo Pedote\nPrysmian Group	18:30 FR\n	46°13.38'N	2020-11-08 17:00:00	03°05.59'W	262°	19.3 kts	17.5 kts	...	260°	20.2 kts	19.0 kts	30.3 nm	252°	2.4 kts	2.4 kts	56.6 nm	24243.6 nm	8.7 nm
46	14	\nFRA 85	Kevin Escoffier\nPRB	18:30 FR\n	46°21.47'N	2020-11-08 17:00:00	03°13.08'W	272°	19.5 kts	15.6 kts	...	270°	21.4 kts	18.2 kts	32.1 nm	261°	2.5 kts	2.4 kts	59.7 nm	24243.8 nm	8.9 nm
47	15	\nFRA 27	Isabelle Joschke\nMACSF	18:30 FR\n	46°19.27'N	2020-11-08 17:00:00	03°10.22'W	272°	19.8 kts	16.0 kts	...	269°	21.6 kts	18.8 kts	32.4 nm	259°	2.5 kts	2.4 kts	58.2 nm	24244.2 nm	9.2 nm
48	16	\nFRA 53	Maxime Sorel\nV And B Mayenne	18:30 FR\n	46°14.24'N	2020-11-08 17:00:00	03°04.87'W	262°	18.7 kts	17.0 kts	...	262°	19.2 kts	17.8 kts	28.9 nm	253°	5.4 kts	5.2 kts	55.8 nm	24244.5 nm	9.5 nm
49	17	\nFRA 1000	Damien Seguin\nGroupe APICIL	18:30 FR\n	46°11.80'N	2020-11-08 17:00:00	03°02.30'W	260°	19.1 kts	17.6 kts	...	258°	19.1 kts	18.1 kts	28.6 nm	250°	2.3 kts	2.3 kts	54.9 nm	24244.7 nm	9.7 nm

	Rang	Heure	Latitude	Date	Longitude	Cap	Vitesse	VMG	Distance	Cap2	...	Cap3	Vitesse3	VMG3	Distance3	DTF	DTL	Nationalite	Voile	Skipper	Bateau
0	1	15:30	46.412778	2020-11-08 14:00:00	-1.846667	241	32.7804	32.4100	0.5556	357	...	201	0.5556	0.5556	11.2972	44992.3028	0.0000	FRA	18	Louis Burton	Bureau Vallée 2
1	2	15:31	46.409444	2020-11-08 14:00:00	-1.839444	241	20.5572	20.1868	0.7408	357	...	196	0.5556	0.3704	11.1120	44992.8584	0.7408	MON	16	Boris Herrmann	Seaexplorer - Yacht Club De Monaco
2	3	15:30	46.425278	2020-11-08 14:00:00	-1.844167	244	28.7060	28.7060	0.9260	357	...	199	0.3704	0.3704	10.1860	44993.0436	0.9260	FRA	8	Jérémie Beyou	Charal
3	4	15:30	46.419722	2020-11-08 14:00:00	-1.835556	244	24.4464	24.2612	1.2964	357	...	196	0.3704	0.3704	10.3712	44993.4140	1.1112	FRA	59	Thomas Ruyant	LinkedOut
4	5	15:30	46.416389	2020-11-08 14:00:00	-1.832222	246	20.1868	20.1868	0.3704	357	...	195	1.4816	1.2964	10.7416	44993.4140	1.1112	FRA	53	Maxime Sorel	V And B Mayenne

	Rang	Heure	Latitude	Date	Longitude	Cap	Vitesse	VMG	Distance	Cap2	...	Date_lancement	Longueur	Largeur	Tirant_eau	Deplacement_poids	Type_derives	Hauteur_mat	Voile_quille	Surface_voile_pres	Surface_voile_portant
0	1	15:30	46.412778	2020-11-08 14:00:00	-1.846667	241	32.7804	32.4100	0.5556	357	...	2015-06-09	18.28	5.80	4.5	7.6	foils	28.0	acier	300	600
1	2	15:31	46.409444	2020-11-08 14:00:00	-1.839444	241	20.5572	20.1868	0.7408	357	...	2015-08-07	18.28	5.70	4.5	7.6	foils	29.0	acier	290	490
2	3	15:30	46.425278	2020-11-08 14:00:00	-1.844167	244	28.7060	28.7060	0.9260	357	...	2018-08-18	18.28	5.85	4.5	8.0	foils	29.0	acier	320	600
3	4	15:30	46.419722	2020-11-08 14:00:00	-1.835556	244	24.4464	24.2612	1.2964	357	...	2019-09-03	18.28	5.85	4.5	8.0	foils	29.0	acier forgé	350	560
4	5	15:30	46.416389	2020-11-08 14:00:00	-1.832222	246	20.1868	20.1868	0.3704	357	...	2007-09-07	18.28	5.50	4.5	7.7	no foils	29.0	acier	365	700

	Rang	Latitude	Longitude	Cap	Vitesse	VMG	Distance	Cap2	Vitesse2	VMG2	...	Distance3	DTF	DTL	Longueur	Largeur	Tirant_eau	Deplacement_poids	Hauteur_mat	Surface_voile_pres	Surface_voile_portant
count	13638.000000	13638.000000	13638.000000	13638.000000	13638.000000	13638.000000	13638.000000	13638.000000	13638.000000	13638.000000	...	13638.000000	13638.000000	13638.000000	1.363800e+04	13638.000000	13638.0	13638.000000	13638.000000	13638.000000	13638.000000
mean	14.838026	-24.114387	-9.511235	131.807596	24.421631	21.067729	12.198797	131.021484	23.920336	21.109962	...	549.006103	26299.921796	2911.667862	1.828000e+01	5.721994	4.5	8.184030	28.459774	302.236398	587.258396
std	8.429013	32.527781	77.527343	81.426934	7.327940	8.638833	3.841107	81.277703	7.174699	8.063772	...	154.860690	12454.152112	2861.838792	1.069406e-12	0.152098	0.0	0.475185	0.783162	30.657757	49.317548
min	1.000000	-58.927222	-179.991944	0.000000	0.000000	-22.594400	0.000000	0.000000	0.000000	-15.927200	...	0.000000	149.086000	0.000000	1.828000e+01	5.300000	4.5	7.000000	26.000000	240.000000	470.000000
25%	8.000000	-48.786111	-35.198194	80.000000	20.001600	15.927200	10.000800	80.000000	19.631200	16.297600	...	453.554800	15736.212500	585.787600	1.828000e+01	5.600000	4.5	7.800000	28.000000	280.000000	560.000000
50%	15.000000	-40.195139	-26.670278	108.000000	24.816800	21.853600	12.408400	106.000000	24.446400	21.668400	...	558.655800	27245.605400	1657.725200	1.828000e+01	5.800000	4.5	8.000000	29.000000	300.000000	580.000000
75%	22.000000	-0.872431	24.345694	182.000000	29.632000	27.409600	14.816000	182.000000	29.076400	27.039200	...	659.312000	37350.024800	4997.205300	1.828000e+01	5.850000	4.5	8.500000	29.000000	320.000000	610.000000
max	33.000000	47.893611	179.960000	360.000000	45.003600	44.633200	60.190000	360.000000	44.262800	43.151600	...	954.335600	45000.636800	14961.196800	1.828000e+01	5.900000	4.5	9.000000	29.000000	365.000000	700.000000

Projet final : Kit Big Data¶

1. Importation des données¶

1.1 Importation des classements¶

1.1.1 Importation des fichiers excels¶

1.1.2 Nettoyage du dataframe¶

Réécriture de certaines variables¶

Conversion des données de latitude et longétude en coordonnées décimales afin de pouvoir mieux les exploiter par la suite.¶

Changement de formats des variables¶

Conversion de certains ordres de grandeurs¶

1.2 Importation des caractéristiques des skippers¶

1.2.1 Web scrapping¶

1.2.2 Nettoyage du dataframe¶

Correction du noms de certains skippers à partir du premier dataframe¶

Correction des noms d'architectes¶

Correction des noms de chantiers¶

Correction d'un numéro de voile manquant à partir du premier dataframe¶

Correction du poids de déplacements manquants de 2 skippers à partir de recherches sur internet¶

Correction de certaines valeure du voile de quille à partir de recherche sur internet¶

Réécriture des variables Nb_derives et Voile_quille¶

Reformatage des variables¶

2. Jointure des 2 tables¶

Jointure des 2 dataframe en un seul¶

3. Analyse Descriptive¶

3.1 Analyse des variables¶

3.1.1 Analyse uni-variée des variables quantitatives¶

Statistiques descriptives des variables numériques¶

Boxplots des distributions¶

3.1.1 Analyse uni-variée des variables qualitatives¶

3.2 Analyse bi-variée et multivariée¶

3.2.1 Matrice des corélations¶

3.2.2 Analyse de l'impact des foils sur les classements et vitesses moyennes des voiliers¶

3.2.3 Analyse de l'impact des caractéristiques techniques des voiliers sur le classement et la vitesse moyenne¶

3.2.4 Analyse de l'impact de la présence d'un foil sur le classement et la vitesse d'un voilier¶

3.2.5 Analyse de la corrélation entre le classement et la vitesse utile (VMG) des voiliers¶

3.2.6 Visualisation des distances et des classements des voiliers¶

4. Distances et Mapping¶

4.1 Visualisation des distances totales parcourues par skippers¶

Classement des distances totales parcourues¶

Tableau des distances parcourues et du classement final¶

4.2 Mapping des parcours des 3 premiers du VendéeGlobe 2020¶

	Skipper	Num_voile	Anciens_noms_bateau	Architecte	Chantier	Date_lancement	Longueur	Largeur	Tirant_eau	Deplacement_poids	Nb_derives	Hauteur_mat	Voile_quille	Surface_voile_pres	Surface_voile_portant
0	Fabrice Amedeo	56	No Way Back, Vento di Sardegna	VPLP/Verdier	Persico Marine	01 Août 2015	18.28	5.85	4.50	7	foils	29	monotype	320	570
1	Romain Attanasio	49	Gitana Eighty, Synerciel, Newrest-Matmut	Bruce Farr Design	Southern Ocean Marine (Nouvelle Zélande)	08 Mars 2007	18.28	5.80	4.50	9	2	28	acier forgé	280	560
2	Alexia Barrier	72	Famille Mary-Etamine du Lys, Initiatives Coeur...	Marc Lombard	MAG France	01 Mars 1998	18.28	5.54	4.50	9	2	29	acier	260	580
3	Yannick Bestaven	17	Safran 2 - Des Voiles et Vous	Verdier - VPLP	CDK Technologies	12 Mars 2015	18.28	5.80	4.50	8	foils	29	acier mécano soudé	310	550
4	Jérémie Beyou	08	NaN	VPLP	CDK Technologies	18 Août 2018	18.28	5.85	4.50	8	foils	29	acier	320	600

	Skipper	Distance2
31	Jérémie Beyou	65.9312
32	Charlie Dalin	64.8200
33	Thomas Ruyant	66.3016
34	Alex Thomson	60.9308
35	Boris Herrmann	62.9680
...	...	...
13633	Manuel Cousin	76.1172
13634	Miranda Merron	65.7460
13635	Clément Giraud	56.1156
13636	Alexia Barrier	50.5596
13637	Ari Huusela	60.1900